/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package is.bean.entity.department;

import is.bean.entity.BeanRoutinesIntegerId;
import is.bean.entity.NamedEntity;
import java.sql.*;
import java.util.Collection;
import java.util.LinkedList;
import javax.ejb.*;
import nullutil.NullUtil;

/**
 *
 * @author aspr0
 */
public class DepartmentBean extends BeanRoutinesIntegerId implements EntityBean
{
    private Department data = new Department();

    @Override
    public void setEntityContext(EntityContext aContext)
    {
        super.setEntityContext(aContext);
        tableName = "department";
    }

    public Integer ejbCreateInstance(Department data) throws CreateException, SQLException
    {
        Connection connection = null;
        PreparedStatement statement = null;
        Integer createdId = null;
        ResultSet resultSet = null;

        try
        {
            boolean setId = false;
            boolean shifted = false;
            Integer dataId = data.getId();
            connection = dataSource.getConnection();

            if (NullUtil.isPositiveInteger(dataId))
            {
                shifted = shiftSequence("DEPARTMENT_SEQ", dataId);
                setId = true;
            }

            if (shifted || !setId)
            {
                String sql = "select department_seq.nextval as id from dual";

                statement = connection.prepareStatement(sql);
                resultSet = statement.executeQuery();
                resultSet.next();
                createdId = resultSet.getInt("id");
            }
            else
            {
                createdId = dataId;
            }

            String sql = "insert "
                    + "into department "
                    + "(id, name, regionId, parentId, bossId) "
                    + "values "
                    + "(?, ?, ?, ?, ?)";
            statement = connection.prepareStatement(sql);

            statement.setInt(1, createdId);
            NullUtil.setStringNullDecide(statement, 2, data.getName());
            NullUtil.setIntNullDecide(statement, 3, data.getRegionId());
            NullUtil.setIntNullDecide(statement, 4, data.getParentId());
            NullUtil.setIntNullDecide(statement, 5, data.getBossId());

            statement.execute();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            CreateException ce = new CreateException();
            ce.initCause(e);
            throw ce;
        }
        finally
        {
            if (statement != null)
            {
                statement.close();
            }

            if (connection != null)
            {
                connection.close();
            }

            if (resultSet != null)
            {
                resultSet.close();
            }
        }

//        id = data.getId();
        this.data = data;
        return createdId;
    }

    public void ejbPostCreateInstance(Department data)
    {
    }

    /**
     *
     * @param example
     * @param start Element to start with, first is 1.
     * @param end
     * @return
     * @throws SQLException
     */
    public Collection ejbFindByExample(Department example, int start, int end) throws SQLException
    {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection<Integer> foundIds = new LinkedList<Integer>();

        try
        {
            Integer idE = example.getId();
            String nameE = example.getName();
            Integer parentIdE = example.getParentId();
            Integer regionIdE = example.getRegionId();
            Integer bossIdE = example.getBossId();

            /* Internal "Where" */
            String where = "";
            String delimiter = "and ";

            if (idE != null)
            {
                where += "regexp_like(id, ?) ";
            }
            if (nameE != null)
            {
                where += ((where.length() > 0) ? delimiter : "") + "regexp_like(name, ?) ";
            }
            if (parentIdE != null)
            {
                where += ((where.length() > 0) ? delimiter : "") + "parentId = ? ";
            }
            if (regionIdE != null)
            {
                where += ((where.length() > 0) ? delimiter : "") + "regionId = ? ";
            }
            if (bossIdE != null)
            {
                where += ((where.length() > 0) ? delimiter : "") + "bossId = ? ";
            }
            if (where.length() > 0)
            {
                where = "where " + where;
            }

            /* Query */
            String sql =
                    "select id from "
                    + "("
                    + "select q.*, rownum rn from "
                    + "("
                    + "select id from department %s order by id"
                    + ") q "
                    + "where rownum <= ?"
                    + ") where rn >= ?";

            sql = String.format(sql, where);

            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql);
            Integer parameterIndex = 1;

            if (idE != null)
            {
                statement.setInt(parameterIndex++, idE);
            }
            if (nameE != null)
            {
                statement.setString(parameterIndex++, nameE);
            }
            if (parentIdE != null)
            {
                statement.setInt(parameterIndex++, parentIdE);
            }
            if (regionIdE != null)
            {
                statement.setInt(parameterIndex++, regionIdE);
            }
            if (bossIdE != null)
            {
                statement.setInt(parameterIndex++, bossIdE);
            }

            statement.setInt(parameterIndex++, end);
            statement.setInt(parameterIndex++, start);

            resultSet = statement.executeQuery();

            while (resultSet.next())
            {
                foundIds.add(resultSet.getInt("id"));
            }
        }
        finally
        {
            if (resultSet != null)
            {
                resultSet.close();
            }

            if (statement != null)
            {
                statement.close();
            }

            if (connection != null)
            {
                connection.close();
            }
        }

        return foundIds;
    }

    public int ejbHomeCountFind(Department example) throws SQLException
    {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int count;

        try
        {
            Integer idE = example.getId();
            String nameE = example.getName();
            Integer parentIdE = example.getParentId();
            Integer regionIdE = example.getRegionId();
            Integer bossIdE = example.getBossId();

            /* Query 1: count */
            String sql = "select count(id) count from department %s";

            String where = "";
            String delimiter = "and ";

            if (idE != null)
            {
                where += "regexp_like(id, ?) ";
            }
            if (nameE != null)
            {
                where += ((where.length() > 0) ? delimiter : "") + "regexp_like(name, ?) ";
            }
            if (parentIdE != null)
            {
                where += ((where.length() > 0) ? delimiter : "") + "parentId = ? ";
            }
            if (regionIdE != null)
            {
                where += ((where.length() > 0) ? delimiter : "") + "regionId = ? ";
            }
            if (bossIdE != null)
            {
                where += ((where.length() > 0) ? delimiter : "") + "bossId = ? ";
            }
            if (where.length() > 0)
            {
                where = "where " + where;
            }

            sql = String.format(sql, where);

            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql);
            Integer parameterIndex = 1;

            if (idE != null)
            {
                statement.setInt(parameterIndex++, idE);
            }
            if (nameE != null)
            {
                statement.setString(parameterIndex++, nameE);
            }
            if (parentIdE != null)
            {
                statement.setInt(parameterIndex++, parentIdE);
            }
            if (regionIdE != null)
            {
                statement.setInt(parameterIndex++, regionIdE);
            }
            if (bossIdE != null)
            {
                statement.setInt(parameterIndex++, bossIdE);
            }

            resultSet = statement.executeQuery();
            resultSet.next();

            count = resultSet.getInt("count");
        }
        finally
        {
            if (resultSet != null)
            {
                resultSet.close();
            }

            if (statement != null)
            {
                statement.close();
            }

            if (connection != null)
            {
                connection.close();
            }
        }

        return count;
    }

    public Department getData()
    {
        return data;
    }

    /**
     * For unified methods
     *
     * @return
     */
    public NamedEntity getNamedEntity()
    {
        return data;
    }

    public void setData(Department data)
    {
        this.data = data;
        modified = true;
    }

    @Override
    protected void loadData() throws SQLException
    {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try
        {
            connection = dataSource.getConnection();
            String sql = "select * from department where id = ?";
            statement = connection.prepareStatement(sql);

            id = (Integer) context.getPrimaryKey(); //todo resolve primary key after ejbCreate unset

            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            resultSet.next();

            data.setId(resultSet.getInt("id"));
            data.setName(resultSet.getString("name"));
            data.setParentId(resultSet.getInt("parentId"));
            data.setRegionId(resultSet.getInt("regionId"));
            data.setBossId(resultSet.getInt("bossId"));
        }
        finally
        {
            if (resultSet != null)
            {
                resultSet.close();
            }

            if (statement != null)
            {
                statement.close();
            }
            if (connection != null)
            {
                connection.close();
            }
        }
    }

    @Override
    protected void storeData() throws SQLException
    {
        if (modified)
        {
            Connection connection = null;
            PreparedStatement statement = null;

            try
            {
                connection = dataSource.getConnection();
                String sql = "update department set name = ?, parentId = ?, regionId = ?, bossId = ? where id = ?";
                statement = connection.prepareStatement(sql);

                NullUtil.setStringNullDecide(statement, 1, data.getName());
                NullUtil.setIntNullDecide(statement, 2, data.getParentId());
                NullUtil.setIntNullDecide(statement, 3, data.getRegionId());
                NullUtil.setIntNullDecide(statement, 4, data.getBossId());
                NullUtil.setIntNullDecide(statement, 5, id);
                statement.execute();
            }
            finally
            {
                if (statement != null)
                {
                    statement.close();
                }

                if (connection != null)
                {
                    connection.close();
                }
            }
        }
    }
}
